package com.cghs.ac.trafficCalc;

import java.io.Serializable;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBService extends SQLiteOpenHelper implements Serializable {
	private static final long serialVersionUID = 3L;
	private final static int DATABASE_VERSION = 1; // ��ݿ�汾
	private final static String DATABASE_NAME = "webtimingclient.db"; // ��ݿ����

	/* ����һ�ε���ʱ����ݿ��еı?���ڣ���Ҫ���� */
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE [t_results] ("
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "[rid] TEXT UNIQUE NOT NULL," + "[tid] TEXT NOT NULL,"
				+ "[date] LONG UNIQUE NOT NULL," + "[url] TEXT NOT NULL,"
				+ "[repeat_count] INTEGER NOT NULL," + "[ip] TEXT,"
				+ "[device_ip] TEXT," + "[isp] TEXT," + "[accesstype] TEXT,"
				+ "[accesslocation] TEXT," + "[timingjson_first] TEXT,"
				+ "[timingjson_repeat] TEXT," + "[firstview_thumbnail] BLOB,"
				+ "[repeatview_thumbnail] BLOB,"
				+ "[firstview_snapshot_path] TEXT,"
				+ "[repeatview_snapshot_path] TEXT,"
				+ "[firstview_video_path] TEXT,"
				+ "[repeatview_video_path] TEXT," + "[firstview_time] BLOB,"
				+ "[repeatview_time] BLOB," + "[dns_results] BLOB,"
				+ "[file_size] LONG," + "[download_time] LONG,"
				+ "[download_speed] DOUBLE," + "[connection_time] LONG,"
				+ "[uploaded] INTEGER DEFAULT 0,"
				+ "[image_uploaded] INTEGER DEFAULT 0,"
				+ "[pcap_uploaded] INTEGER DEFAULT 0,"  
				+ "[dns_server] TEXT," + "[rip] TEXT,"
				+ "[loss] TEXT," + "[delay] DOUBLE," 
				+ "[dns_time] LONG," + "[reponse_time] LONG,"
				+ "[use_time] LONG" + ")");
		db.execSQL("CREATE TABLE [t_options] ("
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "[tid] TEXT UNIQUE NOT NULL," + "[date] LONG NOT NULL,"
				+ "[url] TEXT NOT NULL," + "[repeat_count] INTEGER NOT NULL,"
				+ "[is_firstviewonly] TEXT NOT NULL,"
				+ "[fullpageload_enabled] BOOLEAN," + "[dns_enabled] BOOLEAN,"
				+ "[connection_enabled] BOOLEAN,"
				+ "[download_enabled] BOOLEAN," + "[dns_timeout] LONG,"
				+ "[connection_timeout] LONG," + "[download_timeout] LONG,"
				+ "[download_toplimit] DOUBLE," + "[proxy] TEXT,"
				+ "[dns_server] TEXT," + "[request_header] TEXT,"
				+ "[uploaded] INTEGER DEFAULT 0" + ")");

		db.execSQL("CREATE TABLE [t_servers] ("
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "[rid] LONG UNIQUE NOT NULL," + "[ip] TEXT,"
				+ "[isp] INTEGER," + "[url] TEXT UNIQUE NOT NULL,"
				+ "[area] TEXT," + "[partition] TEXT" + ")");

		db.execSQL("CREATE TABLE [t_speedtest] ("
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT," + "[userid] TEXT,"
				+ "[testid] TEXT," + "[ip] TEXT," + "[latitude] TEXT,"
				+ "[longitude] TEXT," + "[cellid] TEXT," + "[lac] TEXT,"
				+ "[isp] TEXT," + "[accesstype] TEXT," + "[accesslocate] TEXT,"
				+ "[time] LONG," + "[taget_ip] TEXT," + "[speed] TEXT,"
				+ "[loss] TEXT," + "[delay] DOUBLE," + "[avg_area] INTEGER,"
				+ "[avg_isp] INTEGER," + "[rate] INTEGER," + "[rid] TEXT,"
				+ "[area] TEXT," + "[url] TEXT," + "[partition] TEXT,"
				+ "[server_name] TEXT" + ")");
		db.execSQL("CREATE TABLE [t_urltest] ("
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT," + "[userid] TEXT,"
				+ "[testid] TEXT," + "[ip] TEXT," + "[latitude] TEXT,"
				+ "[longitude] TEXT," + "[cellid] TEXT," + "[lac] DOUBLE,"
				+ "[isp] INTEGER," + "[accesstype] TEXT,"
				+ "[accesslocate] TEXT," + "[key] Long," + "[time] Long,"
				+ "[dns_server] TEXT," + "[url] TEXT," + "[rip] TEXT,"
				+ "[loss] TEXT," + "[delay] DOUBLE," + "[filesize] LONG,"
				+ "[speed] DOUBLE," + "[usetime] LONG," + "[nt] LONG,"
				+ "[ct] LONG," + "[rt] LONG," + "[dt] LONG" + ")");
		db.execSQL("CREATE TABLE [t_commandtest] ("
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "[date] LONG UNIQUE NOT NULL," + "[type] TEXT NOT NULL,"
				+ "[url] TEXT," + "[target] TEXT," + "[result] TEXT,"
				+ "[iscompelete] TEXT" + ")");
		db.execSQL("CREATE TABLE [t_url] ("
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "[name] TEXT,"
				+ "[url] TEXT UNIQUE NOT NULL" + ")");
		db.execSQL("CREATE TABLE [traffic] ( " 
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "[month] INTEGER UNIQUE NOT NULL,"
				+ "[wifi] LONG," + "[mobile] LONG,"
				+ "[lastrx] LONG," +"[lasttx] LONG"
				+ ")");
		

	}

	public DBService(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	/* ��Ҫ������ݿ�ʱ���øú��� */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		db.execSQL("drop table if exists [t_results]");
		db.execSQL("drop table if exists [t_options]");
		db.execSQL("drop table if exists [t_servers]");
		db.execSQL("drop table if exists [t_speedtest]");
		// �˴�Ӧ�����µ�SQL���
		db.execSQL("CREATE TABLE [t_results] ("
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "[rid] TEXT UNIQUE NOT NULL," + "[tid] TEXT NOT NULL,"
				+ "[date] LONG UNIQUE NOT NULL," + "[url] TEXT NOT NULL,"
				+ "[repeat_count] INTEGER NOT NULL," + "[ip] TEXT,"
				+ "[device_ip] TEXT," + "[isp] TEXT," + "[accesstype] TEXT,"
				+ "[accesslocation] TEXT," + "[timingjson_first] TEXT,"
				+ "[timingjson_repeat] TEXT," + "[firstview_thumbnail] BLOB,"
				+ "[repeatview_thumbnail] BLOB,"
				+ "[firstview_snapshot_path] TEXT,"
				+ "[repeatview_snapshot_path] TEXT,"
				+ "[firstview_video_path] TEXT,"
				+ "[repeatview_video_path] TEXT," + "[firstview_time] BLOB,"
				+ "[repeatview_time] BLOB," + "[dns_results] BLOB,"
				+ "[file_size] LONG," + "[download_time] LONG,"
				+ "[download_speed] DOUBLE," + "[connection_time] LONG,"
				+ "[uploaded] INTEGER DEFAULT 0,"
				+ "[image_uploaded] INTEGER DEFAULT 0,"
				+ "[pcap_uploaded] INTEGER DEFAULT 0," 
				+ "[dns_server] TEXT," + "[rip] TEXT,"
				+ "[loss] TEXT," + "[delay] DOUBLE," 
				+ "[dns_time] LONG," + "[reponse_time] LONG,"
				+ "[use_time] LONG" + ")");
		db.execSQL("CREATE TABLE [t_options] ("
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "[tid] TEXT UNIQUE NOT NULL," + "[date] LONG NOT NULL,"
				+ "[url] TEXT NOT NULL," + "[repeat_count] INTEGER NOT NULL,"
				+ "[is_firstviewonly] TEXT NOT NULL,"
				+ "[fullpageload_enabled] BOOLEAN," + "[dns_enabled] BOOLEAN,"
				+ "[connection_enabled] BOOLEAN,"
				+ "[download_enabled] BOOLEAN," + "[dns_timeout] LONG,"
				+ "[connection_timeout] LONG," + "[download_timeout] LONG,"
				+ "[download_toplimit] DOUBLE," + "[proxy] TEXT,"
				+ "[dns_server] TEXT," + "[request_header] TEXT,"
				+ "[uploaded] INTEGER DEFAULT 0" + ")");
		db.execSQL("CREATE TABLE [t_servers] ("
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "[rid] LONG UNIQUE NOT NULL," + "[ip] TEXT,"
				+ "[isp] INTEGER," + "[url] TEXT UNIQUE NOT NULL,"
				+ "[area] TEXT," + "[partition] TEXT" + ")");

		db.execSQL("CREATE TABLE [t_speedtest] ("
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT," + "[userid] TEXT,"
				+ "[testid] TEXT," + "[ip] TEXT," + "[latitude] DOUBLE,"
				+ "[longitude] DOUBLE," + "[cellid] TEXT," + "[lac] TEXT,"
				+ "[isp] TEXT," + "[accesstype] TEXT," + "[accesslocate] TEXT,"
				+ "[time] LONG," + "[taget_ip] TEXT," + "[speed] TEXT,"
				+ "[loss] TEXT," + "[delay] DOUBLE," + "[avg_area] INTEGER,"
				+ "[avg_isp] INTEGER," + "[rate] INTEGER," + "[rid] TEXT,"
				+ "[area] TEXT," + "[url] TEXT," + "[partition] TEXT,"
				+ "[server_name] TEXT" + ")");
		db.execSQL("CREATE TABLE [t_urltest] ("
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT," + "[userid] TEXT,"
				+ "[testid] TEXT," + "[ip] TEXT," + "[latitude] DOUBLE,"
				+ "[longitude] DOUBLE," + "[cellid] TEXT," + "[lac] DOUBLE,"
				+ "[isp] INTEGER," + "[accesstype] TEXT,"
				+ "[accesslocate] TEXT," + "[key] Long," + "[time] Long,"
				+ "[dns_server] TEXT," + "[url] TEXT," + "[rip] TEXT,"
				+ "[loss] TEXT," + "[delay] DOUBLE," + "[filesize] LONG,"
				+ "[speed] DOUBLE," + "[usetime] LONG," + "[nt] LONG,"
				+ "[ct] LONG," + "[rt] LONG," + "[dt] LONG" + ")");
		db.execSQL("CREATE TABLE [t_commandtest] ("
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "[date] LONG UNIQUE NOT NULL," + "[type] TEXT NOT NULL,"
				+ "[url] TEXT," + "[target] TEXT," + "[result] TEXT,"
				+ "[iscompelete] TEXT" + ")");
		db.execSQL("CREATE TABLE [t_url] ("
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "[name] TEXT,"
				+ "[url] TEXT UNIQUE NOT NULL" + ")");
		
		db.execSQL("CREATE TABLE [traffic] ( " 
				+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "[month] INTEGER UNIQUE NOT NULL,"
				+ "[wifi] LONG," + "[mobile] LONG,"
				+ "[lastrx] LONG," +"[lasttx] LONG"
				+ ")");

	}

	// ִ��insert��update��delete��SQL���
	public void execSQL(String sql, Object[] args) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL(sql, args);
		// db.close();
	}

	// ִ��select���
	public Cursor query(String sql, String[] args) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, args);
		// db.close();
		return cursor;
	}
}
